SET NOCOUNT ON
USE Northgale
go
CREATE OR ALTER PROCEDURE static_search_5
       @orderid     int          = NULL,
       @status      char(1)      = NULL,
       @fromdate    date         = NULL,
       @todate      date         = NULL,
       @custid      nchar(5)     = NULL,
       @custname    nvarchar(40) = NULL,
       @city        nvarchar(25) = NULL,
       @region      nvarchar(15) = NULL,
       @prodid      int          = NULL,
       @prodname    nvarchar(40) = NULL,
       -- Extra parameters for CSV och TVP.
       @employeetbl intlist_tbltype READONLY,
       @employeecsv nvarchar(MAX)  = NULL,
       @employeetmp nvarchar(MAX)  = NULL AS

DECLARE @hastable bit = CASE WHEN EXISTS (SELECT * FROM @employeetbl)
                             THEN 1
                             ELSE 0
                        END

-- Use this temp table for @employeetmp, be sure to 
-- UPDATE STATISTICS.
CREATE TABLE #emps (empid int NOT NULL PRIMARY KEY)
IF @employeetmp IS NOT NULL
BEGIN
   INSERT #emps(empid)
      SELECT n FROM intlist_to_tbl(@employeetmp)
   UPDATE STATISTICS #emps
END

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CustomerName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder, o.EmployeeID
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.Status = @status OR @status IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CustomerName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)

  -- Extra conditions for the new parameters.
  AND  (o.EmployeeID IN (SELECT val FROM @employeetbl) OR 
       @hastable = 0)

  AND  (o.EmployeeID IN (SELECT n FROM intlist_to_tbl(@employeecsv)) OR
        @employeecsv IS NULL)

  AND  (o.EmployeeID IN (SELECT empid FROM #emps) OR 
        @employeetmp IS NULL)
ORDER  BY o.OrderID
OPTION (RECOMPILE)
go
-- Compare the plans for the three different parameters.
-- 901, 902 and 903 have in total 64 orders, that is few.
DECLARE @tbl intlist_tbltype
INSERT @tbl (val) VALUES (901), (902), (903)
EXEC static_search_5 @employeetbl = @tbl
EXEC static_search_5 @employeecsv = '901,902,903'
EXEC static_search_5 @employeetmp = '901,902,903'
go
-- Try again, now with an employee that has thousands of orders.
DECLARE @tbl intlist_tbltype
INSERT @tbl (val) VALUES (304)
EXEC static_search_5 @employeetbl = @tbl
EXEC static_search_5 @employeecsv = '304'
EXEC static_search_5 @employeetmp = '304'
go
